# -*- coding:utf-8 -*-

import MySQLdb
import ConfigParser
import datetime
import time
from getNextSec import getSecToNext

table_key = ['insertTable', #移动端后台表格
	'articleTable',#网站端所有文章详细信息
	'arcType'#网站端文章类型信息
	]

class SearchDB:
	def __init__(self):
		self.log = []

		config = ConfigParser.ConfigParser()
		with open('db.conf', 'r') as cfgfile:
			config.readfp(cfgfile)
			self.config = config
			host = config.get('db', 'host')
			user = config.get('db', 'user')
			passwd = config.get('db', 'passwd')
			db	 = config.get('db', 'db')
			charset = config.get('db', 'charset')
			yddb = config.get('db', 'yddb')
			
			#self.insert_table = config.get('db', 'table_name')
			# parse table names
			self.table_value={}
			for key in table_key:
				self.table_value[key]=config.get('table',key)


			self.flag = config.get('flag', 'flag')
			time = config.get('lasttime', 'time')
			self.format = config.get('lasttime', 'format')
			if time == '':
				self.time = datetime.datetime(2000,1,1,0,0,0)
			else:
				self.time = datetime.datetime.strptime(time,self.format)
			
			
			self.ydconn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=yddb, charset=charset)
			self.conn = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, charset=charset)
		with open('reflect.txt', 'r') as rfile:
			self.reflect_dict = {}
			for line in rfile.readlines():
				if ':' in line:
					id = line.split(':')[0]
					for typeid in line.replace('\n', '').split(':')[1].split(','):
						self.reflect_dict[typeid] = int(id)
#		print '%s:%s:%s:%s' %(host,user,passwd,db)

	#格式化时间戳为%Y-%m-%d %H:%M:%S
	def formatTime(self, timestamp):
		ltime = time.localtime(timestamp)
		timeStr = time.strftime('%Y-%m-%d %H:%M:%S', ltime)
		return timeStr

	#获取article内容
	def getBody(self, aid):
		article_cursor = self.conn.cursor()
		sql = 'select body from %s where aid=%s' %(self.table_value[table_key[1]],aid)
		result = article_cursor.execute(sql)
		if result > 0:
			body = article_cursor.fetchone()
			return body[0]
		else:
			return None
		article_cursor.close()
		

	#获取关键字(typeid)内容
	def getKeyword(self, typeid):
		article_cursor = self.conn.cursor()
		sql = 'select typename from %s where id=%s' %(self.table_value[table_key[2]],typeid)
		article_cursor.execute(sql)
		body = article_cursor.fetchone()
		article_cursor.close()
		if body is None:
			return ''
		else:
			return body[0]

	def insertDB(self, dataDict):
		# print 'Hello INSERT'
		# print '"%s","%s","%s","%s","%s"'%(dataDict['keyword'],
		# 		dataDict['title'],
		# 		dataDict['typeid'],
		# 		dataDict['releasetime'],
		# 		dataDict['content'])
		insert_cursor = self.ydconn.cursor()
		# id----keyword----title----type_id----releasetime----content
		#INSERT INTO `szyddb`.`newsmodule_news` (`keyword`, `title`, `type_id`, `releasetime`, `content`) VALUES ('1', '1', 1, '2012-07-29 02:24:54', '12312');
		
		#查询是否已经存在于记录中
		sql = 'SELECT * FROM %s  WHERE title = "%s" AND releasetime = "%s"' %(	self.table_value[table_key[0]],dataDict['title'],dataDict['releasetime'])
		#print sql
		result = insert_cursor.execute(sql)
		if result == 0:
			#如果不存在，则将新闻插入数据库
			sql = 'INSERT INTO %s ' %(self.table_value[table_key[0]])
			sql +='VALUES (NULL,%s,%s,%s,%s,%s)'
			result = insert_cursor.execute(sql,
				(
					dataDict['keyword'],
					dataDict['title'],
					dataDict['typeid'],
					dataDict['releasetime'],
					dataDict['content']
				) )
			#如果插入失败，返回值为0，抛出异常
			if result == 0:
				raise Exception
			else:
				self.log.append(u'<更新成功> %s' %dataDict['title'])
				return 1
		else:
			self.log.append(u'<已经存在> %s' %dataDict['title'])
			return 0

	def search(self):	
		archives_cursor = self.conn.cursor()

		print u'查询是否有新数据------'

		archives_cursor.execute('SELECT MAX(senddate) from dede_archives')
		maxtime = datetime.datetime.fromtimestamp(archives_cursor.fetchone()[0])
		if self.time > maxtime:
			print '数据库没有新数据\n'
			return
		archives_cursor.execute('SELECT id,typeid,flag,senddate,title from dede_archives where find_in_set(%s,flag) and senddate > %s', (self.flag,time.mktime(self.time.timetuple())))
		result = archives_cursor.fetchall()

		print u'查询完毕,写入新数据------\n'

		# 插入异常数据写入result.txt
		resultFile = open('result.txt', 'wb')
		# 插入数据数量
		news_num = 0
		for a in result:
			id = a[0]
			typeid = a[1]
			keyword = self.getKeyword(typeid)
			flag = a[2]
			senddate = self.formatTime(a[3])
			title = a[4]
			body = self.getBody(id)
			# 文章内容为空 或者不存在id映射值
			if body is None or self.reflect_dict.has_key(str(typeid)) == False:
				log = (u'[%s][%s]\n' % (id, title)).encode('utf-8')
				resultFile.write(log)
				continue
			try:
#				print 'id=%s' % self.reflect_dict[str(typeid)]
				news_num += self.insertDB({'keyword':keyword, 'title':title, 'typeid':int(self.reflect_dict[str(typeid)]), 'releasetime':senddate, 'content':body})
				#news_num = news_num+1
			except Exception:
				self.ydconn.commit()
				raise
		self.ydconn.commit()
		resultFile.close()
		archives_cursor.close()

		print u'写入新数据完毕，日志文件记录------\n'

		#日志文件写回
		now = time.strftime('%x-%X', time.localtime( time.time() ) )
		logfile_handle = open('log.txt','a')
		logfile_handle.write('BEGIN:%s++++++++++++++++++\n' %(now)  )
		for log in self.log:
			logfile_handle.write(log.encode('utf-8')+'\n')
		logfile_handle.write('END:%s--------------------\n\n\n' %(now)  )
		logfile_handle.close()

		# time 写回  有点问题，没有写回
		self.config.set('lasttime', 'time', maxtime.strftime(self.format))
		self.config.write(open("db.conf", "w"))
		print u'<%s> 插入完毕，一共更新%s条新闻，最新的新闻日期为%s' %(now,news_num,maxtime.strftime(self.format))
		



if __name__ == '__main__' :
	searchHandle = SearchDB()
	# searchHandle.insertDB({'keyword':"keyword",'title':"title",'typeid':1,'releasetime':"2012-07-29 02:24:54",'content':"body"})
	searchHandle.search()
	while(True):
		second = getSecToNext()
		print u'下一次数据迁移任务执行时间为 %s秒之后+++++++' %second
		time.sleep(second)
		searchHandle.search()


